﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;

namespace DAL.Service.Impl
{
   public class LibraryBookIndentServiceImpl:BaseServiceImpl,ILibraryBookIndentService
    {
       public IList getLibraryBookName(int indentid)
       {
           MyQuery query = new MyQuery();
           query.setQueryString(@"select lb_bookName from joy.LibraryBookIndentDetail as a
                            inner join joy.LibraryBookDetail as b on a.lbid_lbdid=b.lbd_id
                            inner join joy.LibraryBook  as c on b.lbd_lbid=c.lb_id where lbid_lbiid=@id");
           query.addParas("@id", indentid);
           return base.getList(query);
       }

       public IList getLibraryBookIndent(string in_s, string in_e, int pageno, int pagesize, int state,string card,string isbn,string indent, string name, string bookName)
       {
           MyQuery query = new MyQuery();
           StringBuilder sb = new StringBuilder();
           string _sql = " where 1=1";
           if (!String.IsNullOrEmpty(in_s))
           {
               _sql += " and lbi_time >= @in_s";
               query.addParas("@in_s", in_s);
           }
           if (!String.IsNullOrEmpty(in_e))
           {
               in_e = in_e + " 23:59:59";
               _sql += " and lbi_time < @in_e";
               query.addParas("@in_e", in_e);
           }
           if (state > -1)
           {
               _sql += " and lbi_state =" + state;
           }
           if (!String.IsNullOrEmpty(name))
           {
               _sql += " and u_name like '%" + name + "%'";
           }
           if (!String.IsNullOrEmpty(bookName))
           {
               _sql += " and lb_bookName like '%" + bookName + "%'";
           }
           if (!String.IsNullOrEmpty(card))
           {
               _sql += " and uc_card like '%" + card + "%'";
           }
           if (!String.IsNullOrEmpty(isbn))
           {
               _sql += " and lbd_isbn like '%" + isbn + "%'";
           }
           if (!String.IsNullOrEmpty(indent))
           {
               _sql += " and lbi_id like '%" + indent + "%'";
           }
           if (pageno == 1)
           {
               sb.Append(@"SELECT count(lbi_id) as total  from joy.LibraryBookIndent as a 
                            inner join joy.UserInfo as b on a.lbi_uid=b.u_id
                            inner join joy.LibraryBookIndentDetail as c on a.lbi_id=c.lbid_lbiid
                            inner join joy.LibraryBookDetail as d on c.lbid_lbdid=d.lbd_id
                            inner join joy.LibraryBook  as e on d.lbd_lbid=e.lb_id
                            inner join joy.UserCard as f on a.lbi_ucid=f.uc_id" + _sql + ";");
               sb.Append("select top " + pagesize + @" * from  joy.LibraryBookIndent as a 
                        inner join joy.UserInfo as b on a.lbi_uid=b.u_id
                        inner join joy.LibraryBookIndentDetail as c on a.lbi_id=c.lbid_lbiid
                        inner join joy.LibraryBookDetail as d on c.lbid_lbdid=d.lbd_id
                        inner join joy.LibraryBook  as e on d.lbd_lbid=e.lb_id
                        inner join joy.UserCard as f on a.lbi_ucid=f.uc_id " + _sql + "  order by lbi_id desc;");
           }
           else
           {
               sb.Append("select * from(");
               sb.Append(@"select *,ROW_NUMBER() OVER (order by lbi_id desc) as rno from   joy.LibraryBookIndent as a 
                            inner join joy.UserInfo as b on a.lbi_uid=b.u_id
                            inner join joy.LibraryBookIndentDetail as c on a.lbi_id=c.lbid_lbiid
                            inner join joy.LibraryBookDetail as d on c.lbid_lbdid=d.lbd_id
                            inner join joy.LibraryBook  as e on d.lbd_lbid=e.lb_id
                            inner join joy.UserCard as f on a.lbi_ucid=f.uc_id" + _sql);
               sb.Append(") as a where a.rno>@start and a.rno<@end  order by lbi_id desc;");
               query.PageNo = pageno;
               query.PageSize = pagesize; ;
           }
           query.setQueryString(sb.ToString());
           return base.getList(query);
       }

       public IList getLibraryBookIndent(string in_s, string in_e, int pageno, int pagesize, int state, string name, string bookName, string author, string press)
       {
           MyQuery query = new MyQuery();
           StringBuilder sb = new StringBuilder();
           string _sql = " where 1=1";
           if (!String.IsNullOrEmpty(in_s))
           {
               _sql += " and lbi_time >= @in_s";
               query.addParas("@in_s", in_s);
           }
           if (!String.IsNullOrEmpty(in_e))
           {
               in_e = in_e + " 23:59:59";
               _sql += " and lbi_time < @in_e";
               query.addParas("@in_e", in_e);
           }
           if (state>-1)
           {
               _sql += " and lbi_state =" + state;
           }
           if (!String.IsNullOrEmpty(name))
           {
               _sql += " and u_name like '%" + name + "%'";
           }
           if (!String.IsNullOrEmpty(bookName))
           {
               _sql += " and lb_bookName like '%" + bookName + "%'";
           }
           if (!String.IsNullOrEmpty(author))
           {
               _sql += " and lb_author like '%" + author + "%'";
           }
           if (!String.IsNullOrEmpty(press))
           {
               _sql += " and lb_press like '%" + press + "%'";
           }
           if (pageno == 1)
           {
               sb.Append("SELECT count(a.lbi_id) as total FROM joy.LibraryBookIndent a inner join joy.UserInfo b  on a.lbi_uid = b.u_id inner join joy.LibraryBook c on a.lbi_lbid=c.lb_id" + _sql + ";");
               sb.Append("select top " + pagesize + " a.*,b.u_name,c.lb_bookName,c.lb_author,c.lb_press from joy.LibraryBookIndent a inner join joy.UserInfo b  on a.lbi_uid = b.u_id inner join joy.LibraryBook c on a.lbi_lbid=c.lb_id" + _sql + "  order by lbi_id desc;");
           }
           else
           {
               sb.Append("select * from(");
               sb.Append("select a.*,b.u_name,c.lb_bookName,c.lb_author,c.lb_press,ROW_NUMBER() OVER (order by a.lbi_id desc) as rno from  joy.LibraryBookIndent a inner join joy.UserInfo b  on a.lbi_uid = b.u_id inner join joy.LibraryBook c on a.lbi_lbid=c.lb_id" + _sql);
               sb.Append(") as a where a.rno>@start and a.rno<@end  order by a.lbi_id desc;");
               query.PageNo = pageno;
               query.PageSize = pagesize; ;
           }
           query.setQueryString(sb.ToString());
           return base.getList(query);
       }

       public int getTimes(int ucid)
       {
           MyQuery query = new MyQuery();
           query.setQueryString("select joy.getCardTimes(@ucid) dateCount");
           query.addParas("@ucid", ucid);
           Hashtable ht = (Hashtable)this.getList(query)[0];
           return Convert.ToInt32(ht["dateCount"]);
       }

       public int getUserHaveBooks(int uid)
       {
           MyQuery query = new MyQuery();
           query.setQueryString("select count(lbi_id) bookCount from joy.LibraryBookIndent where lbi_uid =@uid AND lbi_state>=0 AND lbi_state<3");
           query.addParas("@uid", uid);
           Hashtable ht = (Hashtable)this.getList(query)[0];
           return Convert.ToInt32(ht["bookCount"]);
       }

       public int updateIndentState(int id, int state)
       {
           MyQuery query = new MyQuery();
           query.setQueryString("UPDATE joy.LibraryBookIndent SET lbi_state=@state WHERE lbi_id=@id");
           query.addParas("@id", id);
           query.addParas("@state", state);
           return this.exeUpdateSql(query);
       }

       public int getIndentCountByUcid(int ucid)
       {
           MyQuery query = new MyQuery();
           query.setQueryString("select count(lbi_id) as total from joy.LibraryBookIndent where lbi_state between 0 and 2 and lbi_ucid=@ucid");
           query.addParas("@ucid", ucid);
           Hashtable ht = (Hashtable)this.getList(query)[0];
           return Convert.ToInt32(ht["total"]);
       }
           //
       public int getCountByBookIdUid(int bookid,int uid)
       {
           MyQuery query = new MyQuery();
            query.setQueryString(@"select count(*) as total from joy.LibraryBookIndent as a
            inner join joy.LibraryBookIndentDetail as b
            on a.lbi_id=b.lbid_lbiid
            inner join joy.LibraryBookDetail as c
            on b.lbid_lbdid=c.lbd_id where lbd_lbid=@id and lbi_uid=@uid");
            query.addParas("@id", bookid);
            query.addParas("@uid", uid);
            IList list = base.getList(query);
            Hashtable tb = (Hashtable)list[0];
            return int.Parse(tb["total"].ToString());
       }


       public IList LibraryBookIndentDetail(int pageno, int pagesize, int uid)
       {
           MyQuery query = new MyQuery();
           StringBuilder sb = new StringBuilder();
           string _sql = " where 1 = 1 and lbi_uid=@uid";
           if (pageno == 1)
           {
               sb.Append(@"SELECT count(lbid_id) as total  from joy.LibraryBookIndentDetail as a 
                        inner join joy.LibraryBookDetail as b 
                        on a.lbid_lbdid=b.lbd_id
                        inner join joy.LibraryBook as c
                        on b.lbd_lbid=c.lb_id
                        inner join joy.LibraryBookIndent as d 
                        on a.lbid_lbiid=d.lbi_id" + _sql + ";");
               sb.Append("select top " + pagesize + @" * from joy.LibraryBookIndentDetail as a 
                        inner join joy.LibraryBookDetail as b 
                        on a.lbid_lbdid=b.lbd_id
                        inner join joy.LibraryBook as c
                        on b.lbd_lbid=c.lb_id 
                        inner join joy.LibraryBookIndent as d 
                        on a.lbid_lbiid=d.lbi_id" + _sql + "  order by lbid_id desc;");
           }
           else
           {
               sb.Append("select * from(");
               sb.Append(@"select *,ROW_NUMBER() OVER (order by lbid_id desc) as rno from  joy.LibraryBookIndentDetail as a 
                            inner join joy.LibraryBookDetail as b 
                            on a.lbid_lbdid=b.lbd_id
                            inner join joy.LibraryBook as c
                            on b.lbd_lbid=c.lb_id
                            inner join joy.LibraryBookIndent as d 
                            on a.lbid_lbiid=d.lbi_id" + _sql);
               sb.Append(") as e where e.rno>@start and e.rno<@end  order by lbid_id desc;");
               query.PageNo = pageno;
               query.PageSize = pagesize; ;
           }
           query.addParas("@uid", uid);
           query.setQueryString(sb.ToString());
           return base.getList(query);
       }

       public int recommend(int id)
       {
           MyQuery query = new MyQuery();
           query.setQueryString("UPDATE joy.LibraryBookIndentDetail SET lbi_recommend=1 WHERE lbid_id=@id");
           query.addParas("@id", id);
           return this.exeUpdateSql(query);
       }

       public int  appraisal(int id,string appraisal)
       {
           MyQuery query = new MyQuery();
           query.setQueryString("UPDATE joy.LibraryBookIndentDetail SET lbi_appraisal=@text WHERE lbid_id=@id");
           query.addParas("@id", id);
           query.addParas("@text", appraisal);
           return this.exeUpdateSql(query);
       }

       public IList indent(string lbid, string ip, string ucid, int uid)
       {
           MyQuery query = new MyQuery();
           query.addParas("@lbid_list", lbid);
           query.addParas("@ip", ip);
           query.addParas("@ucid", ucid);
           query.addParas("@uid", uid);
           return base.getList("joy.proIndent", query);
       }
       //   [joy].[proReturnBook]
       //@lbi_id int,--订单id
       //@lbi_stateN tinyint,--新订单状态
       //@lbi_stateO tinyint,--旧订单状态
       //@operUid int--操作员ID
       public IList ReturnBook(string lbi_id, int lbi_stateN, int lbi_stateO, int operUid)
       {
           MyQuery query = new MyQuery();
           query.addParas("@lbi_id", lbi_id);
           query.addParas("@lbi_stateN", lbi_stateN);
           query.addParas("@lbi_stateO", lbi_stateO);
           query.addParas("@operUid", operUid);
           return base.getList("joy.proReturnBook", query);
       }
   
    }
}
